﻿
' Script to Update db
'CREATE TABLE `transactions` (
'  `id` int(10) NOT NULL AUTO_INCREMENT,
'  `owner` varchar(10) NOT NULL,
'  `name` varchar(100) NOT NULL,
'  `type` varchar(45) NOT NULL,
'  `date` date NOT NULL,
'  `amount` decimal(10,2) NOT NULL,
'  `payer` varchar(100) NOT NULL,
'  `remark` varchar(100) NOT NULL,
'  `modifyby` varchar(100) NOT NULL DEFAULT '',
'  `modifydt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
'  PRIMARY KEY (`id`)
') ENGINE=InnoDB AUTO_INCREMENT=1642 DEFAULT CHARSET=utf8;
'/*!40101 SET character_set_client = @saved_cs_client */;

'insert into transactions (owner, name, type, date, amount, payer, remark,modifyby, modifydt) SELECT 'IA', name, type, date, amount, payer, remark, modifyby, modifydt FROM baserecords;

'insert into transactions (owner, name, type, date, amount, payer, remark,modifyby, modifydt) SELECT payer, name, type, date, amount, payer, remark, modifyby, modifydt FROM personrecords;

Public Class Transaction

    Property Handler As DataRowMappedPropertiesHandler = New DataRowMappedPropertiesHandler(Me, "transactions", True)
    Property Row As DataRow

    Public Sub New(row As DataRow)
        Me.Row = row
        Me.Handler.RefreshPropertiesFromRow()
    End Sub

    Public Sub New()
    End Sub

    Public Sub New(ByVal id As Integer)
        Me.Handler.ReadDbRecord(id)
    End Sub

    <DbFieldName("id", True)> Property Id As Integer
    <DbFieldName("owner", False)> Property Owner As String
    <DbFieldName("name", False)> Property Name As String
    <DbFieldName("type", False)> Property RecordType As String
    <DbFieldName("date", False)> Property RecordDate As Date
    <DbFieldName("amount", False)> Property Amount As Decimal
    <DbFieldName("payer", False)> Property Payer As String
    <DbFieldName("remark", False)> Property Remark As String
    <DbFieldName("modifyby", False)> Property ModifyBy As String
    <DbFieldName("modifydt", False)> Property ModifyDatetime As DateTime

    Public ReadOnly Property RecordDateToString As String
        Get
            Return Me.RecordDate.ToString("yyyy-MM")
        End Get
    End Property

    Public Shared Function GetMonthlyRecord(
                                            ByVal owner As String,
                                            ByVal dateFrom As String,
                                            ByVal dateTo As String) As IEnumerable(Of Transaction)
        Dim result As List(Of Transaction) = New List(Of Transaction)
        Dim sql As String = String.Format("select * from transactions where owner = '{0}' and date >= '{1}' and date <'{2}';", owner, dateFrom, dateTo)
        Dim myTable As DataTable = MysqlDataBaseConnection.RunSelect(sql).Tables(0)
        For Each row As DataRow In myTable.Rows
            result.Add(New Transaction(row))
        Next
        Return result
    End Function

    Public Sub Create()
        Me.Handler.InsertDbRecord()

        Dim monthlyRecord As MonthlySummary = MonthlySummary.FindByOwnerAndPeriod(Me.Owner, Me.RecordDateToString)
        If Me.RecordType = "垫付" Or Me.RecordType = "支出" Then
            monthlyRecord.Spend = monthlyRecord.Spend + Me.Amount
            monthlyRecord.Update()
        ElseIf Me.RecordType = "存入" Or Me.RecordType = "收入" Then
            monthlyRecord.Income = monthlyRecord.Income + Me.Amount
            monthlyRecord.Update()
        End If
    End Sub

    Public Sub Update()
        Dim oldRecord As Transaction = New Transaction(Id)
        Dim monthlyRecord As MonthlySummary = MonthlySummary.FindByOwnerAndPeriod(Me.Owner, Me.RecordDateToString)

        ' Delete old amount
        If oldRecord.RecordType = "垫付" Or oldRecord.RecordType = "支出" Then
            monthlyRecord.Spend = monthlyRecord.Spend - oldRecord.Amount
        ElseIf oldRecord.RecordType = "存入" Or oldRecord.RecordType = "收入" Then
            monthlyRecord.Income = monthlyRecord.Income - oldRecord.Amount
        End If

        ' Add new amount
        If Me.RecordType = "垫付" Or Me.RecordType = "支出" Then
            monthlyRecord.Spend = monthlyRecord.Spend + Me.Amount
            monthlyRecord.Update()
        ElseIf Me.RecordType = "存入" Or Me.RecordType = "收入" Then
            monthlyRecord.Income = monthlyRecord.Income + Me.Amount
            monthlyRecord.Update()
        End If

        Me.Handler.UpdateDbRecord()
    End Sub

    Public Sub Delete()
        Dim monthlyRecord As MonthlySummary = MonthlySummary.FindByOwnerAndPeriod(Me.Owner, Me.RecordDateToString)
        If Me.RecordType = "垫付" Or Me.RecordType = "支出" Then
            monthlyRecord.Spend = monthlyRecord.Spend - Me.Amount
            monthlyRecord.Update()
        ElseIf Me.RecordType = "存入" Or Me.RecordType = "收入" Then
            monthlyRecord.Income = monthlyRecord.Income - Me.Amount
            monthlyRecord.Update()
        End If

        Me.Handler.DeleteDbRecord()
    End Sub

End Class